package com.zafu.dao;

import com.zafu.entity.AccountInfo;
import com.zafu.entity.MessageInfo;
import com.zafu.util.ElmDbConn;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class MessageBean {
    private Connection conn;

    public MessageBean() {
    }

    /**添加消息**/
    public int Add_Message(MessageInfo info){
        int count = -1;
        conn =  ElmDbConn.GetConnection();

        if (conn == null) {
            System.out.println("数据库连接失败");
            return -1;
        }

        try {
            StringBuffer sBuffer = new StringBuffer();
            sBuffer.append("insert into Message")
                    .append("(Message_Name, Message_Image, Message, Message_Date, Id)")
                    .append("values ( ?, ?, ?, ?, ?) ");
            PreparedStatement st = conn.prepareStatement( sBuffer.toString() );
            int i = 0;
            st.setString(++i, info.getMessage_Name());
            st.setString(++i, info.getMessage_Image());
            st.setString(++i, info.getMessage());
            st.setString(++i, info.getMessage_Date());
            st.setInt(++i, info.getId());

            count = st.executeUpdate();
            System.out.printf("插入成功\n", count);
            return 0;
        }
        catch (SQLException e){
            System.out.println("插入失败："+ e.getMessage());
            e.printStackTrace();
            return -1;
        }
        finally {
            if (conn != null) {
                try{
                    conn.close();
                }
                catch (SQLException e) {
                    System.out.println("关闭连接失败\n" + e.getMessage());
                }
            }
        }
    }
    /**获得所有消息**/
    public ArrayList<MessageInfo> GetAll() {
        conn = ElmDbConn.GetConnection();
        System.out.println(conn);

        ArrayList<MessageInfo> list = new ArrayList<>();
        try {
            StringBuffer sBuffer = new StringBuffer();
            sBuffer.append( " select * ")
                    .append(" from Message");
            PreparedStatement st = conn.prepareStatement( sBuffer.toString());
            ResultSet rs = st.executeQuery();
            while(rs.next()) {
                MessageInfo info = GetDataFromResultSet(rs);
                list.add(info);
            }
        }
        catch (SQLException e) {
            System.out.printf("数据库查询失败\n" + e.getMessage());
            e.printStackTrace();
        }
        finally {
            if (conn != null) {
                try{
                    conn.close();
                }
                catch ( SQLException e ) {
                    System.out.printf("关闭连接失败\n" + e.getMessage());
                }
            }
        }
        return list;
    }

    /**删除消息**/
    public int Delete_Message(String messageName) {
        int count = -1;
        conn = ElmDbConn.GetConnection();

        if (conn == null) {
            System.out.println("数据库连接失败");
            return -1;
        }

        try {
            StringBuffer sBuffer = new StringBuffer();
            sBuffer.append("DELETE FROM Message WHERE Message_Name = ?");
            PreparedStatement st = conn.prepareStatement(sBuffer.toString());
            st.setString(1, messageName);  // 设置要删除的消息名称

            count = st.executeUpdate();
            if (count > 0) {
                System.out.println("消息删除成功");
            } else {
                System.out.println("未找到指定的消息");
            }
            return count;  // 返回删除的记录数
        }
        catch (SQLException e){
            System.out.println("插入失败："+ e.getMessage());
            e.printStackTrace();
            return -1;
        }
        finally {
            if (conn != null) {
                try{
                    conn.close();
                }
                catch (SQLException e) {
                    System.out.println("关闭连接失败\n" + e.getMessage());
                }
            }
        }
    }

    /**删除所有消息**/
    public int DeleteAll() {
        int count = -1;
        conn = ElmDbConn.GetConnection();
        System.out.println(conn);

        try {
            StringBuffer sBuffer = new StringBuffer();
            sBuffer.append("DELETE FROM Message");
            PreparedStatement st = conn.prepareStatement(sBuffer.toString());

            // 执行删除操作
            count = st.executeUpdate();
            if (count > 0) {
                System.out.println("所有消息已删除");
            } else {
                System.out.println("没有消息需要删除");
            }
            return count;  // 返回删除的记录数
        }
        catch (SQLException e) {
            System.out.printf("数据库查询失败\n" + e.getMessage());
            e.printStackTrace();
            return -1;
        }
        finally {
            if (conn != null) {
                try{
                    conn.close();
                }
                catch ( SQLException e ) {
                    System.out.printf("关闭连接失败\n" + e.getMessage());
                }
            }
        }
    }

    private MessageInfo GetDataFromResultSet(ResultSet rs ){
        MessageInfo info = null;
        try{
            String name = rs.getString("Message_Name");
            String image = rs.getString( "Message_Image" );
            String message = rs.getString( "Message" );
            String date = rs.getString( "Message_Date" );
            int id = rs.getInt("Id");

            info = new MessageInfo(name, image, message, date, id);
        }catch( SQLException e ){
            System.out.printf( "数据库查询失败\n" + e.getMessage()  );
            e.printStackTrace();
        }
        return info;
    }


}
